package com.suncnpap.datamanage.controller;

import com.suncnpap.journal.model.Journal;
import com.suncnpap.journal.service.JournalService;
import com.suncnpap.library.model.BorrowBook;
import com.suncnpap.library.model.BorrowBookRecord;
import com.suncnpap.library.model.Library;
import com.suncnpap.library.service.BorrowBookRecordService;
import com.suncnpap.library.service.BorrowBookService;
import com.suncnpap.library.service.LibraryService;
import com.suncnpap.reader.model.Reader;
import com.suncnpap.reader.service.ReaderService;
import com.suncnpap.system.model.School;

import jxl.read.biff.BiffException;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.util.MultiValueMap;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.app.util.beanutil.BeanUtil;
import com.app.util.common.Constants;
import com.app.util.common.DateUtil;
import com.app.util.common.EasyuiGrid;
import com.app.util.common.FileUtil;
import com.app.util.common.HandlerResult;
import com.app.util.common.PathUtil;
import com.app.util.common.ResponseUtil;
import com.app.util.common.SysConfig;
import com.app.util.common.UuidUtil;
import com.app.util.database.mybatis.Page;
import com.app.util.database.mybatis.QueryType;
import com.suncnpap.base.common.GlobaUser;
import com.suncnpap.base.common.ParamModel;
import com.suncnpap.base.controller.BaseController;
import com.suncnpap.datamanage.common.DataUtil;

/** 
 * 说明：期刊Controller
 */
@Controller
@RequestMapping(value="/datamanage")
public class DataManageController extends BaseController {
	
	
	@Autowired
	ReaderService readerService;
	
	@Autowired
	LibraryService libraryService;
	
	@Autowired
	BorrowBookRecordService borrowBookRecordService;
	
	@Autowired
	BorrowBookService borrowBookService;
	
	@RequestMapping(value="/import")
	public void importExcel(HttpServletRequest request, HttpServletResponse response) throws Exception{
		
		MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;

		HandlerResult result = this.checkToken(multiRequest);
		
		if (result.getCode()==0) {
			String strCreatId = GlobaUser.getStrId(multiRequest.getParameter("token"));//创建人ID
			String strCreatName = GlobaUser.getStrCname(multiRequest.getParameter("token"));//创建人姓名
			String strSchoolId = GlobaUser.getStrSchoolId(multiRequest.getParameter("token"));//独立机构ID
		
			String F_Name = UuidUtil.get32UUID()+".xls";// 最终保存的文件名称
			String F_Path = SysConfig.getProString("PATH_COMM_FILE")+"/import/"+new SimpleDateFormat("yyyy-MM-dd").format(new Date())+"/";
			String allPath = PathUtil.getClasspath() + F_Path + F_Name;
			
			// 使用getMultiFileMap获取objFile对象值在
			MultiValueMap<String, MultipartFile> fileMap = multiRequest.getMultiFileMap();
			/** 开始fileMap对象的foreach循环 **/
			for (Entry<String, List<MultipartFile>> fileList : fileMap.entrySet()) {

				for (int i = 0; i < fileList.getValue().size(); i++) {
					
					MultipartFile multipartFile =  fileList.getValue().get(i);
					
					File file = new File(allPath); 
					FileUtils.copyInputStreamToFile(multipartFile.getInputStream(), file);  
					List<Map<String,Object>> mapList = new ArrayList<Map<String, Object>>();	
					String type = multiRequest.getParameter("type");
					if(type!=null && type.equals("reader")){//读者
						mapList = getExcelToReader(allPath, strCreatId, strCreatName, strSchoolId);
				        List<Reader> readerList = (List<Reader>) JSONArray.parseArray(JSON.toJSONString(mapList), Reader.class);
				        readerService.addList(readerList);
				        result.put("readerList", readerList);
				        result.setMessage("导入成功");
		    		}else if(type!=null && type.equals("library")){//
		    			mapList = getExcelToLibrary(allPath, strCreatId, strCreatName, strSchoolId);
						List<Library> libraryList = (List<Library>) JSONArray.parseArray(JSON.toJSONString(mapList), Library.class);
						libraryService.addList(libraryList);
						result.setMessage("导入成功");
					}else if(type!=null && type.equals("borrowBook")){//
		    			mapList = getExcelToBorrowRecord(allPath, strCreatId, strCreatName, strSchoolId);
						List<BorrowBook> borrowBookList = (List<BorrowBook>) JSONArray.parseArray(JSON.toJSONString(mapList), BorrowBook.class);
						borrowBookService.addList(borrowBookList);
						result.setMessage("导入成功");
					}else if(type!=null && type.equals("borrowBookRecord")){
						mapList = getExcelToBorrowRecord(allPath, strCreatId, strCreatName, strSchoolId);
						List<BorrowBookRecord> borrowBookRecordList = (List<BorrowBookRecord>) JSONArray.parseArray(JSON.toJSONString(mapList), BorrowBookRecord.class);
						borrowBookRecordService.addList(borrowBookRecordList);
						result.setMessage("导入成功");
					}
				}
			}
			
		}
		
		ResponseUtil.writeJson(result, response);

	}
	
	public static List<Map<String,Object>> getExcelToReader(String allPath, String strCreatId, String strCreatName, String strSchoolId){
		List<Map<String,Object>> mapList = new ArrayList<Map<String, Object>>();
		
		FileInputStream inp = null;
		Workbook wb = null;
		try {
			inp = new FileInputStream(allPath);

			wb = WorkbookFactory.create(inp);
			Sheet sheet = wb.getSheetAt(0);
			for (Row row : sheet) {//读取行
				if(row.getRowNum()!=0){
					Map<String,Object> map = new HashMap<String,Object>();
					//读取列
//					姓名	性别	卡号	工号	书屋	所属部门	类型	级	班	学段	
//					证件号码	有效证件	联系方式	登记日期	操作员	电子邮箱	备注

					map.put("strName", convertCell(row.getCell(0)));
					String strSex = convertCell(row.getCell(1));
					map.put("strSex", strSex.equals("男")?"1":"0");
					map.put("strCardNumber", convertCell(row.getCell(2)));
					map.put("strStudentNumber", convertCell(row.getCell(3)));
					map.put("strCollectionLocation", convertCell(row.getCell(4)));
					map.put("strUnitName", convertCell(row.getCell(5)));
					map.put("strReaderType", convertCell(row.getCell(6)));
					map.put("strGrade", convertCell(row.getCell(7)));
					map.put("strClass", convertCell(row.getCell(8)));
					map.put("strStudyPhase", convertCell(row.getCell(9)));
					
					map.put("strIDNumber", convertCell(row.getCell(10)));
					map.put("strValidDocumentation", convertCell(row.getCell(11)));
					map.put("strContact", convertCell(row.getCell(12)));
					
					Date dtRecordDate = new Date();
					if(!convertCell(row.getCell(13)).equals("")){
						dtRecordDate = DateUtil.parseDate(convertCell(row.getCell(13)), "yyyy-MM-dd HH:mm");
					}
					map.put("dtRecordDate", dtRecordDate);
					
					if(!convertCell(row.getCell(14)).equals("")){
						strCreatName = convertCell(row.getCell(14));
					}
					
					map.put("strEmailaddr", convertCell(row.getCell(15)));
					map.put("strRemark", convertCell(row.getCell(16)));
					
					map.put("strId", UuidUtil.get32UUID());
					map.put("strCreatId", strCreatId);
					map.put("strCreatName", strCreatName);
					map.put("strSchoolId", strSchoolId);
					map.put("dtCreatDate", dtRecordDate);
//					
					mapList.add(map);
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		
		return mapList;
	}
	
	public static List<Map<String,Object>> getExcelToLibrary(String allPath, String strCreatId, String strCreatName, String strSchoolId){
		List<Map<String,Object>> mapList = new ArrayList<Map<String, Object>>();
		
		FileInputStream inp = null;
		Workbook wb = null;
		try {
			inp = new FileInputStream(allPath);

			wb = WorkbookFactory.create(inp);
			Sheet sheet = wb.getSheetAt(0);
			for (Row row : sheet) {//读取行
				if(row.getRowNum()!=0){
				//读取列
//				正题名	第一责任者	版本	分册号	分册名	副题名	丛书题名	
//				分类号	附件	每套册数	每套价格	货币单位	合人民币	装订	尺寸开本	
//				页卷数	出版日期	ISBN	出版者	出版地	书次号	来源	订购套数	
//				DBID	录入日期	批号	图书条码	登录号	馆藏位置	书架	索书号	
//				分类名	扩展字段	检索
//					
//					正题名	装订	第一责任者	卷页数	其他责任者	副题名
//					并列题名	主题词	版本	卷册名	丛书题名	ISBN	出版日期
//					出版地	出版社	附注	分类号	每套册数	书次号	每册价格
//					卷册	条形码	馆藏位置	书架

					Map<String,Object> map = new HashMap<String,Object>();
					map.put("strPositiveTitle", convertCell(row.getCell(0)));
					map.put("strBinding", convertCell(row.getCell(1)));
					map.put("strFirstResponsible", convertCell(row.getCell(2)));
					map.put("strNumberPages", convertCell(row.getCell(3)));
					map.put("strOtherResponsible", convertCell(row.getCell(4)));
					map.put("strSubtitle", convertCell(row.getCell(5)));
					map.put("strParallelTitle", convertCell(row.getCell(6)));
					map.put("strKeywords", convertCell(row.getCell(7)));
					
					map.put("strVersion", convertCell(row.getCell(8)));
					map.put("strSectionName", convertCell(row.getCell(9)));
					map.put("strSeriesTitle", convertCell(row.getCell(10)));
					map.put("strISBN", convertCell(row.getCell(11)));
					map.put("strPublicationDate", convertCell(row.getCell(12)));
					map.put("strPublisherPlace", convertCell(row.getCell(13)));
					map.put("strPublisher", convertCell(row.getCell(14)));
					map.put("strRemark", convertCell(row.getCell(15)));
					
					map.put("strClassNumber", convertCell(row.getCell(16)));
					map.put("intNumberCopies", convertCell(row.getCell(17)));
					map.put("strBookNumber", convertCell(row.getCell(18)));
					map.put("floatPricePer", convertCell(row.getCell(19)));
					map.put("strSectionNumber", convertCell(row.getCell(20)));
					map.put("strBookBarcode", convertCell(row.getCell(21)));
					
					map.put("strCollectionLocation", convertCell(row.getCell(22)));
					map.put("strBookshelf", convertCell(row.getCell(23)));
					map.put("strLotNumber", convertCell(row.getCell(24)));
					
					map.put("strId", UuidUtil.get32UUID());
					map.put("strCreatId", strCreatId);
					map.put("strCreatName", strCreatName);
					map.put("strSchoolId", strSchoolId);
					map.put("dtCreatDate", new Date());
					mapList.add(map);
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return mapList;
	}
	
	public static List<Map<String,Object>> getExcelToBorrowRecord(String allPath, String strCreatId, String strCreatName, String strSchoolId){
		List<Map<String,Object>> mapList =  new ArrayList<Map<String, Object>>();	
		
		FileInputStream inp = null;
		Workbook wb = null;
		try {
			inp = new FileInputStream(allPath);

			wb = WorkbookFactory.create(inp);
			Sheet sheet = wb.getSheetAt(0);
			for (Row row : sheet) {//读取行
				if(row.getRowNum()!=0){
					Map<String,Object> map = new HashMap<String,Object>();
					//读取列
	//				图书条码	读者卡号	借阅时间	应还时间	操作员
					map.put("strBookBarcode", convertCell(row.getCell(0)));
					map.put("strReaderCardNumber", convertCell(row.getCell(1)));
					map.put("dtBorrowDate", convertCell(row.getCell(2)));
					map.put("dtReturnDate", convertCell(row.getCell(3)));
					map.put("strCreatName", convertCell(row.getCell(4)));
					if(row.getLastCellNum()>5){
						map.put("isReturn", convertCell(row.getCell(5)));
					}
					
					map.put("strId", UuidUtil.get32UUID());
					map.put("strCreatId", strCreatId);
					map.put("strCreatName", strCreatName);
					map.put("strSchoolId", strSchoolId);
					map.put("dtCreatDate", new Date());
					mapList.add(map);
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return mapList;
	}
	
	private static String convertCell(Cell aCell) {
		NumberFormat formater = NumberFormat.getInstance();
		formater.setGroupingUsed(false);
		String cellValue = "";
		if (aCell == null) {
			return cellValue;
		}

		switch (aCell.getCellType()) {
		case HSSFCell.CELL_TYPE_NUMERIC:
			if(HSSFDateUtil.isCellDateFormatted(aCell)){
				SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				cellValue = sdf.format(aCell.getDateCellValue());
			}else{
				cellValue = formater.format(aCell.getNumericCellValue());
			}
			break;
		case HSSFCell.CELL_TYPE_STRING:
			cellValue = aCell.getStringCellValue();
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			cellValue = aCell.getStringCellValue();
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			cellValue = Boolean.valueOf(aCell.getBooleanCellValue()).toString();
			break;
		case HSSFCell.CELL_TYPE_ERROR:
			cellValue = String.valueOf(aCell.getErrorCellValue());
			break;
		default:
			cellValue = "";
		}
		return cellValue.trim();
	}
	
	public static void main(String[] args) {
		
		List<Map<String,Object>> mapList = new ArrayList<Map<String, Object>>();	
		String type = "library";//multiRequest.getParameter("type");
		String allPath = "F:/新建文件夹/123/library (2).xls";
		if(type!=null && type.equals("reader")){//读者
			mapList = getExcelToReader(allPath,"", "", "");
	        List<Reader> readerList = (List<Reader>) JSONArray.parseArray(JSON.toJSONString(mapList), Reader.class);
	        System.out.println(readerList);
//	        readerService.addList(readerList);
		}else if(type!=null && type.equals("library")){//
			mapList = getExcelToLibrary(allPath,"", "", "");
			List<Library> libraryList = (List<Library>) JSONArray.parseArray(JSON.toJSONString(mapList), Library.class);
			System.out.println(libraryList);
//			libraryService.addList(libraryList);
		}else if(type!=null && type.equals("borrowBookRecord")){
			mapList = getExcelToBorrowRecord(allPath,"", "", "");
			List<BorrowBookRecord> borrowBookRecordList = (List<BorrowBookRecord>) JSONArray.parseArray(JSON.toJSONString(mapList), BorrowBookRecord.class);
			System.out.println(borrowBookRecordList);
//			borrowBookRecordService.addList(borrowBookRecordList);
		}
	}
	
	private String[] getCommand() {
	    String[] cmd = new String[3];
	    String time = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString();
	    String os = System.getProperties().getProperty("os.name");
	    if (os.startsWith("Win")) {
	        cmd[0] = "cmd.exe";
	        cmd[1] = "/c";
	    } else {
	        cmd[0] = "/bin/sh";
	        cmd[1] = "-c";
	    }
	    StringBuilder arg = new StringBuilder();
	    arg.append("mysqldump ");
	    arg.append("-h118.25.159.42 ");
	    arg.append("-uplib ");
	    arg.append("-pplib_pass ");
	    arg.append("--default-character=utf-8 ");
	    arg.append("--skip-opt ");
	    arg.append("--add-drop-database ");
	    arg.append("--routines ");
	    arg.append("--triggers ");
	    arg.append("--compress ");
	    arg.append("-r");
	    arg.append("PATH");
	    arg.append(time);
	    arg.append(".sql ");
	    arg.append("--databases plib");
	    cmd[2] = arg.toString();
	    return cmd;
	}  
}
